﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
//using System.Data.SqlClient;
using System.Configuration;
using System.Data.SqlServerCe;

namespace Expense.DataAccess
{
    public class ExpenseDataAccess
    {
        #region "Public Methods"

        /// <summary>
        /// Gets the table object from query.
        /// </summary>
        /// <param name="strConnString">The STR conn string.</param>
        /// <param name="strQueryString">The STR query string.</param>
        /// <param name="dictParameters">The dict parameters.</param>
        /// <param name="commandType">Type of the command.</param>
        /// <returns></returns>
        public DataTable GetTableObjectFromQuery(string strConnString, string strQueryString, Dictionary<string, string> dictParameters, CommandType commandType)
        {
            DataTable dataTable = new DataTable();

            try
            {
                if (VerifyNullInputs(strConnString, strQueryString, dictParameters))
                    dataTable = GetTableData(strConnString, strQueryString, dictParameters, commandType);
                else
                    dataTable = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dataTable;
        }

        /// <summary>
        /// Gets the single object from query.
        /// </summary>
        /// <param name="strConnString">The STR conn string.</param>
        /// <param name="strQueryString">The STR query string.</param>
        /// <param name="dictParameters">The dict parameters.</param>
        /// <param name="commandType">Type of the command.</param>
        /// <returns></returns>
        public object GetSingleObjectFromQuery(string strConnString, string strQueryString, Dictionary<string, string> dictParameters, CommandType commandType)
        {
            object objct = null;

            try
            {
                if (VerifyNullInputs(strConnString, strQueryString, dictParameters))
                    objct = GetObjectData(strConnString, strQueryString, dictParameters, commandType);
                else
                    objct = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return objct;
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="strConnString">The STR conn string.</param>
        /// <param name="strQueryString">The STR query string.</param>
        /// <param name="dictParameters">The dict parameters.</param>
        /// <param name="commandType">Type of the command.</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string strConnString, string strQueryString, Dictionary<string, string> dictParameters, CommandType commandType)
        {
            int value = 1;
            try
            {
                if (VerifyNullInputs(strConnString, strQueryString, dictParameters))
                    value = ExecNonQuery(strConnString, strQueryString, dictParameters, commandType);
                else
                    value = 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return value;
        }

        #endregion

        #region "Private Methods"

        /// <summary>
        /// Verifies the null inputs.
        /// </summary>
        /// <param name="strConnString">The STR conn string.</param>
        /// <param name="strQueryString">The STR query string.</param>
        /// <param name="dictParameters">The dict parameters.</param>
        /// <returns></returns>
        private bool VerifyNullInputs(string strConnString, string strQueryString, Dictionary<string, string> dictParameters)
        {
            bool boolVerified = false;

            if ((strConnString != "" & strQueryString != "") | dictParameters != null)
            {
                boolVerified = true;
            }

            return boolVerified;
        }

        /// <summary>
        /// Gets the connection.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <returns></returns>
        private static SqlCeConnection GetConnection(string name)
        {
            SqlCeConnection dbConnection = new SqlCeConnection();

            dbConnection.ConnectionString = ConfigurationManager.AppSettings[name];

            return dbConnection;
        }

        /// <summary>
        /// Gets the table data.
        /// </summary>
        /// <param name="strConnString">The STR conn string.</param>
        /// <param name="strQueryString">The STR query string.</param>
        /// <param name="dictParameters">The dict parameters.</param>
        /// <param name="commandType">Type of the command.</param>
        /// <returns></returns>
        private DataTable GetTableData(string strConnString, string strQueryString, Dictionary<string, string> dictParameters, CommandType commandType)
        {
            DataTable dataTable = new DataTable();
            using (SqlCeConnection conn = GetConnection(strConnString))
            {
                using (SqlCeCommand cmd = new SqlCeCommand(strQueryString, conn))
                {
                    try
                    {
                        cmd.CommandType = commandType;
                        if (dictParameters != null)
                        {
                            foreach (KeyValuePair<string, string> parameter in dictParameters)
                            {
                                cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
                            }
                        }

                        using (SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd))
                        {
                            adapter.Fill(dataTable);
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }

                }
            }
            return dataTable;
        }

        /// <summary>
        /// Gets the object data.
        /// </summary>
        /// <param name="strConnString">The STR conn string.</param>
        /// <param name="strQueryString">The STR query string.</param>
        /// <param name="dictParameters">The dict parameters.</param>
        /// <param name="commandType">Type of the command.</param>
        /// <returns></returns>
        private object GetObjectData(string strConnString, string strQueryString, Dictionary<string, string> dictParameters, CommandType commandType)
        {
            object objct = null;
            using (SqlCeConnection conn = GetConnection(strConnString))
            {
                using (SqlCeCommand cmd = new SqlCeCommand(strQueryString, conn))
                {
                    try
                    {
                        cmd.CommandType = commandType;
                        if (dictParameters != null)
                        {
                            foreach (KeyValuePair<string, string> parameter in dictParameters)
                            {
                                cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
                            }
                        }

                        conn.Open();
                        using (SqlCeDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                objct = reader.GetValue(0);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }

                    conn.Close();
                }
            }
            return objct;
        }

        /// <summary>
        /// Execs the non query.
        /// </summary>
        /// <param name="strConnString">The STR conn string.</param>
        /// <param name="strQueryString">The STR query string.</param>
        /// <param name="dictParameters">The dict parameters.</param>
        /// <param name="commandType">Type of the command.</param>
        /// <returns></returns>
        private int ExecNonQuery(string strConnString, string strQueryString, Dictionary<string, string> dictParameters, CommandType commandType)
        {
            int value = 1;
            using (SqlCeConnection conn = GetConnection(strConnString))
            {
                using (SqlCeCommand cmd = new SqlCeCommand(strQueryString, conn))
                {
                    try
                    {
                        cmd.CommandType = commandType;
                        if (dictParameters != null)
                        {
                            foreach (KeyValuePair<string, string> parameter in dictParameters)
                            {
                                cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
                            }
                        }

                        cmd.Connection.Open();
                        value = cmd.ExecuteNonQuery();

                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }

                    cmd.Connection.Close();
                }
            }

            return value;
        }
        #endregion

    }
}
